﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.Page;

//调用NOPI 1.2.5版本命名空间
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;//HSSF Excel2003命名空间
using System.IO;
using System.Data;
using System.Text;
using NPOI.XSSF.UserModel;

/// <summary>
///NPOIToExcel 的摘要说明
/// </summary>
public class NPOIToExcel
{
    public NPOIToExcel()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }

    #region 将GridView中的数据导出为Xml文件 表头部分待完善,HSSFWorkbook导出为Excel2003格式

    /// <summary>
    /// 将GridView中的数据导出为Xml文件
    /// </summary>
    /// <param name="gv">需要处理的GridView</param>
    /// <param name="filePath">xml文件存放路径</param>
    public static void ToXml(GridView gv, string filePath)
    {
        IWorkbook hssfworkbook = new HSSFWorkbook();
        ISheet sheet = hssfworkbook.CreateSheet("sheet1");

        IRow row = sheet.CreateRow(0);
        ICell cell;

        ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
        cellStyle.Alignment = HorizontalAlignment.Center;//CENTER_SELECTION
        cellStyle.VerticalAlignment = VerticalAlignment.Center;//CENTER

        int colCount = 0;//记录GridView列数
        int rowInex = 0;//记录表头的行数

        /***************************************/
        /***************添加表头****************/
        /***************************************/

        #region 添加表头

        int groupCount = 0;//记录分组数
        int colIndex = 0;//记录列索引，并于结束表头遍历后记录总列数
        for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
        {
            if (rowInex != groupCount)//新增了标题行时重新创建
            {
                row = sheet.CreateRow(rowInex);
                groupCount = rowInex;
            }

            #region 是否跳过当前单元格

            for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域
            {
                NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
                //当前单元格是处于合并区域内
                if (a.FirstColumn <= colIndex && a.LastColumn >= colIndex
                    && a.FirstRow <= rowInex && a.LastRow >= rowInex)
                {
                    colIndex++;
                    m = 0;//重新遍历所有合并区域判断新单元格是否位于合并区域
                }
            }


            #endregion

            cell = row.CreateCell(colIndex);
            cell.CellStyle = cellStyle;

            TableCell tablecell = gv.HeaderRow.Cells[i];

            //跨列属性可能为添加了html属性colspan，也可能是由cell的ColumnSpan属性指定
            int colSpan = 0;
            int rowSpan = 0;

            #region 获取跨行跨列属性值
            //跨列
            if (!string.IsNullOrEmpty(tablecell.Attributes["colspan"]))
            {
                colSpan = int.Parse(tablecell.Attributes["colspan"].ToString());
                colSpan--;
            }
            if (tablecell.ColumnSpan > 1)
            {
                colSpan = tablecell.ColumnSpan;
                colSpan--;
            }

            //跨行
            if (!string.IsNullOrEmpty(tablecell.Attributes["rowSpan"]))
            {
                rowSpan = int.Parse(tablecell.Attributes["rowSpan"].ToString());
                rowSpan--;
            }
            if (tablecell.RowSpan > 1)
            {
                rowSpan = tablecell.RowSpan;
                rowSpan--;
            }
            #endregion

            //添加excel合并区域
            if (colSpan > 0 || rowSpan > 0)
            {
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + rowSpan, colIndex, colIndex + colSpan));
                colIndex += colSpan + 1;//重新设置列索引
            }
            else
            {
                colIndex++;
            }
            string strHeader = gv.HeaderRow.Cells[i].Text;

            if (strHeader.Contains("</th></tr><tr>"))//换行标记，当只存在一行标题时不存在</th></tr><tr>，此时colCount无法被赋值
            {
                rowInex++;
                colCount = colIndex;
                colIndex = 0;

                strHeader = strHeader.Substring(0, strHeader.IndexOf("</th></tr><tr>"));
            }
            cell.SetCellValue(strHeader);
        }
        if (groupCount == 0)//只有一行标题时另外为colCount赋值
        {
            colCount = colIndex;
        }

        #endregion

        rowInex++;//表头结束后另起一行开始记录控件数据行索引

        /***************************************/
        /***************添加数据****************/
        /***************************************/

        #region 添加数据

        int rowCount = gv.Rows.Count;

        for (int i = 0; i < rowCount; i++)
        {
            row = sheet.CreateRow(rowInex);

            //bool flagPreviousVisible = true;
            for (int j = 0; j < colCount; j++)
            {
                if (gv.Rows[i].Cells[j].Visible == false)
                {
                    //flagPreviousVisible = false;
                    continue;
                }

                #region 判断是否为矩形单元格中的单元格

                if (i > 0 && j > 0)
                {
                    bool flagSkip = false;
                    int tempx = -1;//记录矩形单元格的左下角位置
                    for (int l = j - 1; l >= 0; l--)
                    {
                        if (gv.Rows[i].Cells[l].Visible == false)
                        {
                            tempx = l;
                            break;
                        }
                        else
                        {
                            continue;
                        }
                    }
                    if (tempx > -1)//存在可能的矩形单元格
                    {
                        for (int m = i - 1; m >= 0; m--)
                        {
                            int colSpan = j - tempx + 1;
                            int rowSpan = i - m + 1;
                            if (gv.Rows[m].Cells[tempx].ColumnSpan >= colSpan && gv.Rows[m].Cells[tempx].RowSpan >= rowSpan)//存在矩形单元格，即当前单元格属于矩形单元格中的一部分
                            {
                                flagSkip = true;
                                break;//满足条件，跳出循环
                            }
                            else
                            {
                                continue;
                            }
                        }
                    }
                    if (flagSkip)//存在矩形单元格，且该单元格存在于该矩形单元格，跳过此单元格拼接字符串
                    {
                        continue;
                    }
                }

                #endregion


                cell = row.CreateCell(j);
                cell.CellStyle = cellStyle;
                cell.SetCellType(CellType.String);
                if (!string.IsNullOrEmpty(gv.Rows[i].Cells[j].Text.Replace("&nbsp;", "")))
                {

                    cell.SetCellValue(gv.Rows[i].Cells[j].Text);
                }
                else
                {
                    cell.SetCellValue(" ");
                }


                int MergeAcross = gv.Rows[i].Cells[j].ColumnSpan;//跨列，即合并的列数
                if (MergeAcross > 0)
                {
                    MergeAcross--;
                }
                else
                {
                    MergeAcross = 0;
                }
                //? MergeAcross = MergeAcross - 1 : MergeAcross = 0;
                int MergeDown = gv.Rows[i].Cells[j].RowSpan;//跨行，即合并的行数
                //MergeDown > 0 ? MergeDown-- : MergeDown = 0;
                if (MergeDown > 0)
                {
                    MergeDown--;
                }
                else
                {
                    MergeDown = 0;
                }

                if (MergeAcross > 0 || MergeDown > 0)//存在要合并的行
                {
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + MergeDown, j, j + MergeAcross));
                    j += MergeAcross;
                    //colCount += 1;
                }


                FileStream fs = new FileStream(filePath, FileMode.Create);
                hssfworkbook.Write(fs);
                fs.Close();
            }
            rowInex++;
        }

        #endregion
    }


    #endregion

    #region 已优化----------------------------将GridView中的数据导出为Excel ，XSSFWorkbook导出为Excel2007格式,dll类库更新为NPOI 2.1.1

    /// <summary>
    /// 将GridView中的数据导出为Excel2007文件，已优化
    /// </summary>
    /// <param name="gv">需要处理的GridView</param>
    /// <param name="filePath">xml文件存放路径</param>
    public static void OptimizaToExcel(GridView gv, string filePath)
    {
        IWorkbook hssfworkbook = new XSSFWorkbook();
        ISheet sheet = hssfworkbook.CreateSheet("sheet1");

        IRow row = sheet.CreateRow(0);
        ICell cell;

        ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
        cellStyle.Alignment = HorizontalAlignment.CenterSelection;//CENTER_SELECTION
        cellStyle.VerticalAlignment = VerticalAlignment.Center;//CENTER

        int colCount = 0;//记录GridView列数
        int rowInex = 0;//记录表头的行数

        /***************************************/
        /***************添加表头****************/
        /***************************************/

        #region 添加表头

        int groupCount = 0;//记录分组数
        int colIndex = 0;//记录列索引，并于结束表头遍历后记录总列数
        for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
        {
            if (rowInex != groupCount)//新增了标题行时重新创建
            {
                row = sheet.CreateRow(rowInex);
                groupCount = rowInex;
            }

            #region 是否跳过当前单元格

            for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域
            {
                NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
                //当前单元格是处于合并区域内
                if (a.FirstColumn <= colIndex && a.LastColumn >= colIndex
                    && a.FirstRow <= rowInex && a.LastRow >= rowInex)
                {
                    colIndex++;
                    m = 0;//重新遍历所有合并区域判断新单元格是否位于合并区域
                }
            }


            #endregion

            cell = row.CreateCell(colIndex);
            cell.CellStyle = cellStyle;

            TableCell tablecell = gv.HeaderRow.Cells[i];

            //跨列属性可能为添加了html属性colspan，也可能是由cell的ColumnSpan属性指定
            int colSpan = 0;
            int rowSpan = 0;

            #region 获取跨行跨列属性值
            //跨列
            if (!string.IsNullOrEmpty(tablecell.Attributes["colspan"]))
            {
                colSpan = int.Parse(tablecell.Attributes["colspan"].ToString());
                colSpan--;
            }
            if (tablecell.ColumnSpan > 1)
            {
                colSpan = tablecell.ColumnSpan;
                colSpan--;
            }

            //跨行
            if (!string.IsNullOrEmpty(tablecell.Attributes["rowSpan"]))
            {
                rowSpan = int.Parse(tablecell.Attributes["rowSpan"].ToString());
                rowSpan--;
            }
            if (tablecell.RowSpan > 1)
            {
                rowSpan = tablecell.RowSpan;
                rowSpan--;
            }
            #endregion

            //添加excel合并区域
            if (colSpan > 0 || rowSpan > 0)
            {
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + rowSpan, colIndex, colIndex + colSpan));
                colIndex += colSpan + 1;//重新设置列索引
            }
            else
            {
                colIndex++;
            }
            string strHeader = gv.HeaderRow.Cells[i].Text;

            if (strHeader.Contains("</th></tr><tr>"))//换行标记，当只存在一行标题时不存在</th></tr><tr>，此时colCount无法被赋值
            {
                rowInex++;
                colCount = colIndex;
                colIndex = 0;

                strHeader = strHeader.Substring(0, strHeader.IndexOf("</th></tr><tr>"));
            }
            cell.SetCellValue(strHeader);
        }
        if (groupCount == 0)//只有一行标题时另外为colCount赋值
        {
            colCount = colIndex;
        }

        #endregion

        rowInex++;//表头结束后另起一行开始记录控件数据行索引

        /***************************************/
        /***************添加数据****************/
        /***************************************/

        #region 添加数据

        int rowCount = gv.Rows.Count;

        for (int i = 0; i < rowCount; i++)
        {
            row = sheet.CreateRow(rowInex);

            for (int j = 0; j < colCount; j++)
            {
                if (gv.Rows[i].Cells[j].Visible == false)
                {
                    continue;
                }

                #region 已注释------判断是否为矩形单元格中的单元格

                //if (i > 0 && j > 0)
                //{
                //    bool flagSkip = false;
                //    int tempx = -1;//记录矩形单元格的左下角位置
                //    for (int l = j - 1; l >= 0; l--)
                //    {
                //        if (gv.Rows[i].Cells[l].Visible == false)
                //        {
                //            tempx = l;
                //            break;
                //        }
                //        else
                //        {
                //            continue;
                //        }
                //    }
                //    if (tempx > -1)//存在可能的矩形单元格
                //    {
                //        for (int m = i - 1; m >= 0; m--)
                //        {
                //            int colSpan = j - tempx + 1;
                //            int rowSpan = i - m + 1;
                //            if (gv.Rows[m].Cells[tempx].ColumnSpan >= colSpan && gv.Rows[m].Cells[tempx].RowSpan >= rowSpan)//存在矩形单元格，即当前单元格属于矩形单元格中的一部分
                //            {
                //                flagSkip = true;
                //                break;//满足条件，跳出循环
                //            }
                //            else
                //            {
                //                continue;
                //            }
                //        }
                //    }
                //    if (flagSkip)//存在矩形单元格，且该单元格存在于该矩形单元格，跳过此单元格拼接字符串
                //    {
                //        continue;
                //    }
                //}

                #endregion


                cell = row.CreateCell(j);
                cell.CellStyle = cellStyle;
                cell.SetCellType(CellType.String);//STRING
                if (!string.IsNullOrEmpty(gv.Rows[i].Cells[j].Text.Replace("&nbsp;", "")))
                {

                    cell.SetCellValue(gv.Rows[i].Cells[j].Text);
                }
                else
                {
                    cell.SetCellValue(" ");
                }

                int MergeAcross = gv.Rows[i].Cells[j].ColumnSpan > 0 ? gv.Rows[i].Cells[j].ColumnSpan - 1 : 0;//跨列，即合并的列数
                /*int MergeAcross = gv.Rows[i].Cells[j].ColumnSpan;
                if (MergeAcross > 0)
                {
                    MergeAcross--;
                }
                else
                {
                    MergeAcross = 0;
                }*/

                int MergeDown = gv.Rows[i].Cells[j].RowSpan > 0 ? gv.Rows[i].Cells[j].RowSpan - 1 : 0;//跨行，即合并的行数
                /*int MergeDown = gv.Rows[i].Cells[j].RowSpan;//跨行，即合并的行数
                if (MergeDown > 0)
                {
                    MergeDown--;
                }
                else
                {
                    MergeDown = 0;
                }*/

                if (MergeAcross > 0 || MergeDown > 0)//存在要合并的行
                {
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + MergeDown, j, j + MergeAcross));
                    j += MergeAcross;
                    //colCount += 1;
                }



            }
            rowInex++;
        }

        #endregion

        try
        {
            FileStream fs = File.Create(filePath);//new FileStream(filePath, FileMode.Create);
            hssfworkbook.Write(fs);
            fs.Close();
        }
        catch (Exception e1)
        {
            throw new Exception("errorMessage=" + e1.Message + ";filePath=" + filePath);
        }
        /*将下载文件，Response在类中应以HttpResponse代替，且用于静态方法会出现问题
        Response.Clear();
        Response.BufferOutput = true;
        Response.ContentType = "application/ms-excel";
        Response.AddHeader("Content-Disposition", "attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");//Server.UrlEncode(
        if (File.Exists(filePath))
        {
            Response.WriteFile(filePath);
        }
        Response.Flush();

        File.Delete(filePath);//通知浏览器下载文件

        Response.End();
        */




        //将文件流写入Http流中
        //using (MemoryStream stram = new MemoryStream())
        //{
        //    hssfworkbook.Write(stram);

        //    Response.Clear();
        //    Response.BufferOutput = true;
        //    Response.ContentType = "application/ms-excel";
        //    Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
        //    Response.BinaryWrite(stram.ToArray());
        //    Response.Flush();
        //    Response.End();
        //}

    }


    #endregion


    /*
     MemoryStream stram = new MemoryStream();

        hssfworkbook.Write(stram);
        //stram.Close();
        Response.Clear();
        Response.BufferOutput = true;
        Response.ContentType = "application/ms-excel";
        Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);
        Response.BinaryWrite(stram.ToArray());
        Response.Flush();
        Response.End();
     */
}
